package com.jbm.jbmsupplier.util;
import java.io.IOException;
import java.net.InetSocketAddress;
import java.nio.charset.Charset;
import java.text.SimpleDateFormat;
import java.util.Date;

import org.apache.mina.core.service.IoAcceptor;
import org.apache.mina.core.service.IoHandlerAdapter;
import org.apache.mina.core.session.IoSession;
import org.apache.mina.filter.codec.ProtocolCodecFilter;
import org.apache.mina.filter.codec.textline.TextLineCodecFactory;
import org.apache.mina.transport.socket.nio.NioSocketAcceptor;

import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Connection;
import java.sql.Statement;

import org.slf4j.*;
import java.util.UUID;
import java.security.MessageDigest;
import java.io.RandomAccessFile;


public class MinaServerHandler extends IoHandlerAdapter {
    protected static Logger logger = LoggerFactory.getLogger(MinaServerHandler.class);

    protected  String MD5 ( String s )
    {
        char hexDigits[]={'0','1','2','3','4','5','6','7','8','9','A','B','C','D','E','F'};
        try {
            byte[] btInput = s.getBytes();
            // 获得MD5摘要算法的 MessageDigest 对象
            MessageDigest mdInst = MessageDigest.getInstance("MD5");
            // 使用指定的字节更新摘要
            mdInst.update(btInput);
            // 获得密文
            byte[] md = mdInst.digest();
            // 把密文转换成十六进制的字符串形式
            int j = md.length;
            char str[] = new char[j * 2];
            int k = 0;
            for (int i = 0; i < j; i++) {
                byte byte0 = md[i];
                str[k++] = hexDigits[byte0 >>> 4 & 0xf];
                str[k++] = hexDigits[byte0 & 0xf];
            }
            return new String(str);
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }

    public void exceptionCaught(IoSession session, Throwable cause) throws Exception {
        logger.error("服务端发送异常...", cause);
    }

    public void messageReceived(IoSession session, Object message) throws Exception {
        String msg = message.toString();
        String classname = msg.substring(0,msg.indexOf('@'));
        Connection conn = DriverManager.getConnection("jdbc:mysql://192.168.0.66:3306/jbm?"
                + "user=root&password=password&useUnicode=true&characterEncoding=UTF8");
        Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
        int pver = 0;
        int sver = 3;
        int build = 12;

        if ( classname.equals("com.jbm.jbmsupplier.util.IVersion"))
        {
            IVersion icall = ( IVersion )message;
            if ( pver == icall.pver && sver == icall.sver )
                session.write("OK");
            else
                session.write("Wrong");
        }else if ( classname.equals("com.jbm.jbmsupplier.util.ILogin" ) )
        {
            ILogin icall = ( ILogin )message;
            String sql = "select * from userentity where name = '"+icall.name+"' and password = '"+MD5(icall.password)+"'";
            ResultSet rs = stmt.executeQuery(sql);
            rs.last();
            if ( rs.getRow() == 1 )
                session.write("Login OK");
            else
                session.write("未知的用户名或密码");
        }else if ( classname.equals("com.jbm.jbmsupplier.util.IBreakJob")) {
            IBreakJob icall = ( IBreakJob )message;
            String sql = "update t_workorder set status = 3 where status in ( 0,1 ) and id = "+icall.jobid;
            int rcnt = stmt.executeUpdate( sql );

            if ( rcnt == 1 )
                session.write("BreakJob OK");
            else
                session.write("BreakJob Fail");
        }else if ( classname.equals("com.jbm.jbmsupplier.util.ISubmitJob")) {
            ISubmitJob icall = ( ISubmitJob )message;
            String sql = "update t_workorder set status = 2 where status = 1 and id = "+icall.jobid;
            int rcnt = stmt.executeUpdate( sql );

            if ( rcnt == 1 )
                session.write("SubmitJob OK");
            else
                session.write("SubmitJob Fail");
        }else if ( classname.equals("com.jbm.jbmsupplier.util.IConfirmJob")) {
            IConfirmJob icall = ( IConfirmJob )message;
            String sql = "update t_workorder set status = 1 where status = 0 and id = "+icall.jobid;
            int rcnt = stmt.executeUpdate( sql );

            if ( rcnt == 1 )
                session.write("ConfirmJob OK");
            else
                session.write("ConfirmJob Fail");
        }else if ( classname.equals("com.jbm.jbmsupplier.util.IUploadMedia")) {
            IUploadMedia icall = ( IUploadMedia )message;

            String dbfpath="";
            RandomAccessFile raf;

            conn.setAutoCommit(false);

            if ( icall.media.mtype.equals("text"))
            {
                String sql;
                if (icall.media.mid > 0)
                    sql = "update t_workorder_media set m_text = '" + icall.media.text + "' where id = "+icall.media.mid;
                else
                    sql = "insert into t_workorder_media ( workorder_id, m_type, m_text ) values (" +
                        icall.jobid + ",'" + icall.media.mtype + "','" + icall.media.text + "')";
                int rcnt = stmt.executeUpdate(sql);

                if (rcnt != 1)
                    icall.media.offset = -1;
                else {
                    sql = "select @@identity";
                    ResultSet rs = stmt.executeQuery(sql);
                    rs.next();
                    icall.media.mid = rs.getInt(1);
                    icall.media.filepath = dbfpath;
                }
            }else {
                if (icall.media.mid > 0) {
                    if (icall.media.filepath.equals("")) {
                        String sql = "select * from t_workorder_media where id = " + icall.media.mid;
                        ResultSet rs = stmt.executeQuery(sql);
                        while (rs.next()) {
                            dbfpath = rs.getString("m_filepath");
                        }
                    } else
                        dbfpath = icall.media.filepath;
                    raf = new RandomAccessFile(dbfpath, "rw");
                    if (raf.length() != icall.media.offset)
                        icall.media.offset = -1;
                } else {
                    dbfpath = "d:/media/" + UUID.randomUUID() + '-' + icall.media.filename;
                    String sql = "insert into t_workorder_media ( workorder_id, m_type, m_text, m_filename, m_filepath, m_memo ) values (" +
                            icall.jobid + ",'" + icall.media.mtype + "','" + icall.media.text + "','" + icall.media.filename + "','" + dbfpath + "','"+icall.media.m_memo+"')";
                    int rcnt = stmt.executeUpdate(sql);

                    if (rcnt != 1)
                        icall.media.offset = -1;
                    else {
                        sql = "select @@identity";
                        ResultSet rs = stmt.executeQuery(sql);
                        rs.next();
                        icall.media.mid = rs.getInt(1);
                        icall.media.filepath = dbfpath;
                    }

                    raf = new RandomAccessFile(dbfpath, "rw");
                }

                if (icall.media.offset != -1) {
                    if (icall.bufferlen > 0) {
                        raf.seek(icall.media.offset);
                        raf.write(icall.buffer, 0, icall.bufferlen);
                    }

                    if (icall.bufferlen == 0) {
                        String sql = "update t_workorder_media set status = 'full' where id = " + icall.media.mid;
                        int rcnt = stmt.executeUpdate(sql);

                        if (rcnt != 1)
                            icall.media.offset = -1;
                    } else {
                        icall.media.offset += icall.bufferlen;
                    }
                }
                raf.close();
            }

            if (icall.media.offset == -1)
                conn.rollback();
            else
                conn.commit();
            session.write(icall.media);
        }else if ( classname.equals("com.jbm.jbmsupplier.util.IDownLoadMedia")) {
            IDownloadMedia icall = ( IDownloadMedia )message;
            session.write("Login OK");
        }else if ( classname.equals("com.jbm.jbmsupplier.util.IGetJobList")) {
            IGetJobList icall = ( IGetJobList )message;
            JobList jl = new JobList();
            String sql = "select tt.name team, ue.name user, cc.name catalog, cp.name rtype, tr.name rdesc, tw.* from t_workorder tw "+
                        "left outer join t_team tt on tw.team_id = tt.id "+
                        "left outer join userentity ue on tw.user_id = ue.id "+
                        "left outer join codecatalog cc on tw.catalog_id = cc.id "+
                        "left outer join codeproperty cp on tw.repairtype = cp.id "+
                        "left outer join t_repairdesc tr on tw.repairdesc = tr.id where tw.status in ( 0,1)";
            ResultSet rs = stmt.executeQuery(sql);
            rs.last();
            int rcnt = rs.getRow();
            rs.first();

            if ( rcnt > 0 )
            {
                jl.count = rcnt;
                jl.joblist = new Job[rcnt];

                int i = 0;

                do
                {
                    jl.joblist[i] = new Job();
                    jl.joblist[i].jobid = rs.getInt("id");
                    jl.joblist[i].team = rs.getString("team");
                    jl.joblist[i].user = rs.getString("user");
                    jl.joblist[i].catalog = rs.getString("catalog");
                    jl.joblist[i].rtype = rs.getString("rtype");
                    jl.joblist[i].rdesc = rs.getString("rdesc");
                    jl.joblist[i].mobile = rs.getString("mobile");
                    jl.joblist[i].name = rs.getString("name");
                    jl.joblist[i].address = rs.getString("address");
                    jl.joblist[i].houseno = rs.getString("houseno");
                    jl.joblist[i].create_time = rs.getString("create_time");
                    jl.joblist[i].repaircontent = rs.getString("repaircontent");
                    jl.joblist[i].status = rs.getInt("status");

                    i ++;
                }while ( rs.next() );
            }
            session.write(jl);
        }else if ( classname.equals("com.jbm.jbmsupplier.util.ILoadJob")) {
            ILoadJob icall = ( ILoadJob )message;
            Job job = new Job();
            String sql = "select tt.name team, ue.name user, cc.name catalog, cp.name rtype, tr.name rdesc, tw.* from t_workorder tw "+
                    "left outer join t_team tt on tw.team_id = tt.id "+
                    "left outer join userentity ue on tw.user_id = ue.id "+
                    "left outer join codecatalog cc on tw.catalog_id = cc.id "+
                    "left outer join codeproperty cp on tw.repairtype = cp.id "+
                    "left outer join t_repairdesc tr on tw.repairdesc = tr.id where tw.status in ( 0,1) and tw.id = "+icall.jobid;
            ResultSet rs = stmt.executeQuery(sql);
            rs.last();
            int rcnt = rs.getRow();
            rs.first();

            if ( rcnt > 0 )
            {
                do
                {
                    job.jobid = rs.getInt("id");
                    job.team = rs.getString("team");
                    job.user = rs.getString("user");
                    job.catalog = rs.getString("catalog");
                    job.rtype = rs.getString("rtype");
                    job.rdesc = rs.getString("rdesc");
                    job.mobile = rs.getString("mobile");
                    job.name = rs.getString("name");
                    job.address = rs.getString("address");
                    job.houseno = rs.getString("houseno");
                    job.create_time = rs.getString("create_time");
                    job.repaircontent = rs.getString("repaircontent");
                    job.status = rs.getInt("status");
                }while ( rs.next() );
            }
            session.write(job);
        }else
            session.write( "Unkown class");

        conn.close();
    }

    public void sessionCreated(IoSession session) throws Exception {
        logger.info("服务端与客户端创建连接...");
    }
}
